package com.zy.dao.impl;

import com.zy.dao.DepartmentDao;
import com.zy.pojo.Department;
import com.zy.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: xiaoqi
 * Date: 2021/5/31
 * Time: 下午 16:29
 * Description: 部门持久层接口实现类
 */
public class DepartmentDaoImpl implements DepartmentDao {
    private JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());

    @Override
    public Integer insertDepartment(Department department) {
        String sql = "insert into department (departmentName , createTime) values ( ? , ? )";
        return jdbcTemplate.update(sql, department.getDepartmentName(), department.getCreateTime());
    }

    @Override
    public Integer getDepartmentPeopleCount(Integer id) {
        String sql = "select count(*) from employee t1 left join department t2 on t1.departmentId = t2.id where t2.id = ?";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class, id);
        return count;
    }

    @Override
    public Integer deleteDepartmentById(Integer id) {
        String sql = "delete from department where id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Integer updateDepartment(Department department) {
        String sql = "update department set departmentName = ? where id = ?";
        return jdbcTemplate.update(sql, department.getDepartmentName(), department.getId());
    }

    @Override
    public List<Department> getDepartmentList(Integer page, Integer limit) {
        String sql = "SELECT\n" +
                "\tt3.*,\n" +
                "\tmax( t4.money ) maxSalary,\n" +
                "\tmin( t4.money ) minSalary,\n" +
                "\tavg( t4.money ) avgSalary,\n" +
                "\tt7.count \n" +
                "FROM\n" +
                "\tdepartment t3\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt1.id empId,\n" +
                "\t\tt1.`name`,\n" +
                "\t\tt1.departmentId,\n" +
                "\t\tt2.money \n" +
                "\tFROM\n" +
                "\t\temployee t1\n" +
                "\t\tLEFT JOIN salary t2 ON t1.id = t2.empId \n" +
                "\t) t4 ON t3.id = t4.departmentId\n" +
                "\tLEFT JOIN (\n" +
                "\tSELECT\n" +
                "\t\tt5.id,\n" +
                "\t\tcount( t6.id ) count \n" +
                "\tFROM\n" +
                "\t\tdepartment t5\n" +
                "\t\tLEFT JOIN employee t6 ON t5.id = t6.departmentId \n" +
                "\tGROUP BY\n" +
                "\t\tt5.id \n" +
                "\t) t7 ON t3.id = t7.id \n" +
                "GROUP BY\n" +
                "\tt3.id ";
        List<Department> list = new ArrayList<Department>();
        try {
            if (page != null && limit != null) {
                sql += " limit ? , ? ";
                list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Department>(Department.class), page, limit);
            }else{
                list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Department>(Department.class));
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return list;
        }
    }

    @Override
    public Department getDepartmentById(Integer id) {
        String sql = "select * from department where id = ?";
        Department department = null;
        try {
            department = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Department>(Department.class), id);
        } catch (DataAccessException e) {
            e.printStackTrace();
        } finally {
            return department;
        }
    }

    @Override
    public Integer getDepartmentCount() {
        String sql = "select count(*) from department";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }

}